﻿package batchload;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestBatchLoad001 {
    
    Connection          conn            = null;
    Statement           statement       = null;    
    int                 TEST_VALUE      = 0;
    String              tableName       = null;
    String              inputData       = null;
    int                 columCount      = 0;
    int                 loopCount       = 0;
    
    
    //기본생성자
    public TestBatchLoad001(){
        TEST_VALUE      = 10;
        loopCount       = 1;
        //TEST_VALUE * loopCount 는 10,000 되어야함
        columCount      = 800;
        tableName       = "TEST_TABLE_" + columCount;
        inputData       = "ABCDEFGHIJ";
    }
    
    //DB접속
    public void DBConn(){
        try {
            conn = new db.DBConn().getConnection();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    //입력값
    public void ValueSet(){
        
        do{
            System.out.print("테스트 몇회수행?? [숫자를 넣으세요]: ");
            BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            int input_int;
            
            try {
                input_int = Integer.parseInt(br.readLine().trim());
            } catch (Exception e) {
                continue;
            }
            TEST_VALUE = input_int;
            break;
        }while(true) ;
    }
    
    //batch수행
    public void excute(){
        String  SQL = null;
        
        try {
            statement = conn.createStatement();
            
//            long stime = System.currentTimeMillis(); //시작시간
            conn.setAutoCommit(false);
            for(int i = 0 ; i < TEST_VALUE ; i++){
//                name = "junseok" + i;
                SQL = makeSql(tableName, i, inputData, columCount);
                statement.addBatch(SQL);
            }
            statement.executeBatch();
            conn.setAutoCommit(true);
//            long etime  = System.currentTimeMillis(); //종료시간
//            System.out.println("Total ruunig time : " + (etime - stime) + "ms");
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("SQL error");
        }catch(Exception e){
            e.printStackTrace();
            System.out.println("error");
        }
    }
    
    //건by건
    public void excute2(){
        String  SQL = null;
        
        try {
            statement = conn.createStatement();
            long stime = System.currentTimeMillis(); //시작시간
            for(int i = 0 ; i < TEST_VALUE ; i++){
//                name = "junseok" + i;
                SQL = makeSql(tableName, i, inputData, columCount);
                statement.executeUpdate(SQL);
            }
            long etime  = System.currentTimeMillis(); //종료시간
            System.out.println("Total ruunig time : " + (etime - stime) + "ms");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("SQL error");
        }catch(Exception e){
            e.printStackTrace();
            System.out.println("error");
        }
    }
    
    //sql 작성
    public String makeSql(String tableName, int index, String name, int count){
        StringBuilder SQL = new StringBuilder();
        
        SQL.append("INSERT INTO ");
        SQL.append(tableName);
        SQL.append(" VALUES (");
        
        for(int i = 0 ; i < count ; i++){
            if(i == 0 ) {
                SQL.append(" '" + name +"' ");
            }else{
                SQL.append(" , '" + name + "' ");
            }
        }
        SQL.append(" ) ");
        
//        System.out.println("sql : " + SQL.toString());
//        System.out.println("sql length : "+ SQL.length());
//        System.exit(0);
        
        return SQL.toString();
    }//sql 작성 End
    
    //table 초기화
    public void clearTable(String tableName){
        String clear_sql = null;
        
        try {
            statement = conn.createStatement() ;
            clear_sql ="TRUNCATE TABLE " + tableName;
            statement.execute(clear_sql) ;
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("SQL error");
        } catch (Exception e){
            e.printStackTrace();
            System.out.println("error");
        }
    }//table 초기화 End
    
    //TABLE ROW COUNT CHECK
    public void selectCount(String tableName){
        String select_sql = null;
        ResultSet rs = null;
        int     rowCount = 0;
        
        try {
            statement = conn.createStatement() ;
            select_sql ="SELECT COUNT(1) FROM " + tableName;
            
            rs = statement.executeQuery(select_sql) ;
            
            while(rs.next()){
                rowCount = rs.getInt(1);
            }
            System.out.println("rowCount : "+ rowCount);
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("SQL error");
        } catch (Exception e){
            e.printStackTrace();
            System.out.println("error");
        }
    }
    
    public static void main(String[] args){        
        System.out.println("============================");
        TestBatchLoad001 ob1 = new TestBatchLoad001();
        ob1.DBConn();
        
        
        System.out.println("Statement batch수행");
        System.out.println("tableName  : " + ob1.tableName);
        System.out.println("Insert 단위 : " + ob1.TEST_VALUE);
        System.out.println("loopCount 단위 : " + ob1.loopCount);        
        long stime = System.currentTimeMillis(); //시작시간
        for(int i = 0 ; i < ob1.loopCount ; i++){
            ob1.excute();
        }
        long etime  = System.currentTimeMillis(); //종료시간
        System.out.println("Total ruunig time : " + (etime - stime) + "ms"); //수행시간출력
        ob1.selectCount(ob1.tableName);        
        ob1.clearTable(ob1.tableName);
        
        
        
//        System.out.println("Statement 건by건");
//        for(int i = 0 ; i < 1000 ; i++){
//            ob1.excute2();
//        }
//        ob1.selectCount(ob1.tableName);
//        ob1.clearTable(ob1.tableName);
        
        System.out.println("테이블초기화 완료");    
    }

}
